package com.supermap.wzhy.module.data.service;

import com.supermap.wzhy.common.service.BaseService;
import com.supermap.wzhy.data.CnToSpell;
import com.supermap.wzhy.data.ExcelUtil;
import com.supermap.wzhy.data.RegionSupport;
import com.supermap.wzhy.data.Sort;
import com.supermap.wzhy.data.cons.CMicroMetaType;
import com.supermap.wzhy.data.cons.CMicroTableType;
import com.supermap.wzhy.data.cons.SysConstant;
import com.supermap.wzhy.entity.*;
import com.supermap.wzhy.module.data.dao.MicroIdenmetaDao;
import com.supermap.wzhy.module.data.dao.MicroTablemetaDao;
import com.supermap.wzhy.module.sys.dao.RegionCatalogsDao;
import com.supermap.wzhy.module.sys.dao.RegionInfoDao;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.*;

/**
 * Created by W.Qiong on 14-3-4.
 * 基层元数据
 */
@Service
public class MicroTablemetaService extends BaseService {
    @Autowired
    private MicroTablemetaDao microTablemetaDao;
    @Autowired
    private RegionCatalogsDao regionCatalogsDao;
    @Autowired
    private MicroIdenmetaDao microIdenmetaDao;
    @Autowired
    RegionInfoDao regionInfoDao;

    /**
     * 创建调查对象
     *
     * @param rcid
     * @param entity
     * @return
     */
    public TMicroTablemeta create(int rcid, TMicroTablemeta entity) {
        if (checkObjIsExist(entity)) {
            return null;
        }
        if (entity != null) {
            String cnName = entity.getName();
            TRegioncatalog rcatalog = regionCatalogsDao.findOne(rcid);
            //可以自己指定表名
            if(null == entity.getTableName()){
                String tablename = this.generateMicObjectTableName(cnName);    // 根据中文名 解析出英文名
                entity.setTableName(tablename);
            }
            String tablename = entity.getTableName() ;
            entity.setTRegioncatalog(rcatalog);
            entity.setStatus(1);
            //默认是调查对象
            entity.setMicmetaType(entity.getMicmetaType()==0 ? CMicroMetaType.OBJECT_TYPE : entity.getMicmetaType());
            String creSql = createEntitySql(entity.getParid(), tablename);
//            if(entity.getMicmetaType() == 1) { //调查对象，暂不添加month和year字段
//                creSql = creSql.replace("YEAR NUMBER,MONTH NUMBER,", "");
//            }
            int parid=entity.getParid();
            entity.setParid(0);
            TMicroTablemeta tablemeta = microTablemetaDao.save(entity);
            //先创建实体表 再创建调查对象
            try {
                microTablemetaDao.executeBySql(creSql);
            }catch (Exception e){
                System.out.println("创建调查对象实体表错误，检查表名"+tablename+"是否存在已经存在");
            }
            this.createMicroObjecIdenmeta(tablemeta.getMitmid(), parid);
            indexAndComment(tablename, cnName, getIdenCodeList(tablemeta.getMitmid()));
            return tablemeta;
        }
        return null;
    }

    // 创建基层模板
    public TMicroTablemeta create(TMicroTablemeta entity) {
        if (checkObjIsExist(entity)) {
            return null;
        }
        if (entity != null) {
            entity.setStatus(1);
            entity.setMicmetaType(3); // 3 是模板
            TMicroTablemeta tablemeta = microTablemetaDao.save(entity);
            return tablemeta;
        }
        return null;
    }

    /**
     * 删除基层对象 包括调查对象、模板等
     *
     * @param id
     * @return
     */
    public TMicroTablemeta delete(int id) {
        TMicroTablemeta tablemeta = microTablemetaDao.findOne(id);
        if (tablemeta != null) {
            boolean isSuccess = false ;
            boolean isDelTable = false ;
            int metaType = tablemeta.getMicmetaType();
            if (metaType == CMicroMetaType.OBJECT_TYPE || metaType == CMicroMetaType.RANGE_TYPE) {
                isDelTable = true ;
                int tableType = tablemeta.getMictableType();
                if (tableType == CMicroTableType.ACTUAL_TABLE_TYPE) {
                    isSuccess=deleteTable(tablemeta.getTableName());
                } else if (tableType == CMicroTableType.VIEW_TYPE) {
                    isSuccess=deleteView(tablemeta.getTableName());
                }
            }
            //删除实体表成功 或者直接删除指标模板
            if((isDelTable&&isSuccess)||(!isDelTable&&!isSuccess)){
                microTablemetaDao.delete(id);
            }
            return tablemeta;
        }
        return null;
    }

    public boolean update(int id, TMicroTablemeta entity) {
        if (microTablemetaDao.exists(entity.getMitmid())) {
            microTablemetaDao.save(entity);
            return true;
        }
        return false;
    }

    public Page<TMicroTablemeta> query(TMicroTablemeta entity, PageRequest pageRequest) {
        return microTablemetaDao.findAll(pageRequest);
    }

    public TMicroTablemeta one(int id) {
        return microTablemetaDao.findOne(id);
    }

    public List<TMicroTablemeta> findMicros(int metaType) {
        return microTablemetaDao.findByMicmetaType(metaType);
    }

    //查询基层数据列表
    public List<TMicroTablemeta> findMicroTablemetas(int metaType, int catalogId) {
        return microTablemetaDao.findByTypeAndCatalogId(metaType, catalogId);
    }
    //查询基层数据列表
    public List<TMicroTablemeta> findMicroTablemetas(int metaType, int catalogId, String moduleId) {
        return microTablemetaDao.findByTypeAndCatalogId(metaType, catalogId, moduleId);
    }

    public List<TMicroTablemeta> findMicrosByMajorAndReportType(int metaType,int catalog,int reportType,int major){
        if(catalog==-1){
            if(major==-1){
                return microTablemetaDao.findByType(metaType,reportType);
            }else{
                return microTablemetaDao.findByTypeAndMajor(metaType,reportType,major);
            }
        }else{
            if (major == -1) {
                return microTablemetaDao.findByTypeAndCatalog(metaType,catalog,reportType);
            }else{
                return microTablemetaDao.findByTypeAndMajorAndCatalog(metaType,catalog,reportType,major);
            }
        }
    }

    /**
     * 根据调查对象中文名获取英文表名  首字母连接+序号
     *
     * @return
     */
    public String generateMicObjectTableName(String name) {
        String tableName = "T_MICRODATA_";
        tableName += CnToSpell.getEn(name);
        List<TMicroTablemeta> existList = microTablemetaDao.findByTableName(tableName);
        int max = -1;
        for (TMicroTablemeta t : existList) {
//            System.out.println(t.getTableName());
            String sub = t.getTableName().substring(tableName.length());
            int num = 0;
            try {
                //前几位相同 可能位数要大于，可能报错
                num = Integer.parseInt(sub != null && !sub.equals("") ? sub : "0");
            } catch (Exception e) {
                num = 0;
            }
            if (num > max) {
                max = num;
            }
        }
        if (max != -1) {
            tableName += (max + 1 + "");
        }
        return tableName;
    }

    /**
     * 检查调查对象名称是否被占用  同一个时段同一个专业一个名称只能用一次
     *
     * @param tablemeta
     * @return
     */
    public boolean checkObjIsExist(TMicroTablemeta tablemeta) {
        int micmetatype = tablemeta.getMicmetaType();
        int reporttype = tablemeta.getReportType();
        String name = tablemeta.getName();
        TUsermajor marjor = tablemeta.gettUsermajor() ;
        int majorid = null!=marjor?marjor.getMajorid():0;
        TMicroTablemeta t = microTablemetaDao.findByCondition(micmetatype, reporttype, name, majorid);
        if (t != null) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 创建调查对象实体表 根据模板
     *
     * @param parid
     * @param tableName
     * @return
     */
    public String createEntitySql(int parid, String tableName) {
        String sql = "create table " + tableName + "(";
        sql += "ID NUMBER(10)  NOT NULL PRIMARY KEY,SMX NUMBER,SMY NUMBER,A00011X varchar(2),YEAR NUMBER,MONTH NUMBER,";
        Map<String, String> map = new HashMap<>();
        map.put("A00011X", "行业大类");//避免行业大类重复
        if (parid != 0) {
            List<TMicroIdenmeta> idenList = microIdenmetaDao.findObjectIdens(parid);
            System.out.println(idenList.size());
            for (TMicroIdenmeta t : idenList) {
                int idenType = t.getIdenType();
                String idenCode = t.getIdenCode();
                int idenLen = t.getIdenLength();
                //避免重复列
                if (map.containsKey(idenCode)
                        || idenCode.equalsIgnoreCase("A00011X")) {
                    continue;
                }
                map.put(idenCode, t.getIdenName());
                sql += idenCode;
                if (idenType == 2) {
                    int preci = t.getIdenPrecision();
                    sql += " number(" + (preci > 0 ? (idenLen > 0 ? idenLen : 10 + "," + preci) :
                            (idenLen > 0 && idenLen <= 38 ? idenLen : 10)) + "), ";
                } else if (idenType == 3) {
                    sql += " Date ,";
                } else {
                    sql += " varchar(" + (idenLen > 0 ? idenLen : 300) + "), ";
                }
            }
        }
        sql = sql.substring(0, sql.lastIndexOf(","));
        sql += ")";
        return sql;
    }

    /**
     * 创建调查对象系统默认指标元数据
     *
     * @param objectid
     * @param parid
     * @return
     */
    public boolean createMicroObjecIdenmeta(int objectid, int parid) {
        TMicroTablemeta tablemeta = microTablemetaDao.findOne(objectid);
        if (tablemeta != null) {
            if (parid != 0) {
                List<TMicroIdenmeta> idenList = microIdenmetaDao.findObjectIdens(parid);
                for (TMicroIdenmeta t : idenList) {
                    TMicroIdenmeta tempIden = new TMicroIdenmeta();
                    String[] exclude = {"miimid"};
                    tempIden = (TMicroIdenmeta) convertBeanTOBean(t, tempIden, exclude);
                    tempIden.setTMicroTablemeta(tablemeta);
                    microIdenmetaDao.save(tempIden);
                }
            }
            return true;
        }
        return false;
    }

    /**
     * 删除实体表
     *
     * @param tablename
     * @return
     */
    public boolean deleteTable(String tablename) {
        int reVal = -1 ;
        try{
            reVal = microTablemetaDao.executeBySql("drop table " + tablename);
            return true;
        }
        catch (Exception e){
            reVal = -1;
            return false;
        }
        //return reVal != -1 ? true : false;
    }

    /**
     * 删除视图
     *
     * @param viewname
     * @return
     */
    public boolean deleteView(String viewname) {
        int reVal = -1 ;
        try{
            reVal = microTablemetaDao.executeBySql("drop view " + viewname);
        }
        catch (Exception e){
            reVal = 1;
        }
        return reVal >= 1 ? true : false;
    }


    public boolean importData(String tableName, List<String> data) {
        return false;
    }

    public boolean indexAndComment(String tableName, String cnName, List<TMicroIdenmeta> idenList) {
        createIndex(tableName, idenList);
        addComments(tableName, cnName, idenList);
        return true;
    }

    private boolean createIndex(String tableName, List<TMicroIdenmeta> idenList) {

        for (TMicroIdenmeta meta : idenList) {
            String strSql = "";
            String ideCode = meta.getIdenCode();
            if (ideCode.equals("ID")) {
                strSql = "alter table " + tableName + " add constraint " + tableName + "_PK primary key (" + ideCode + ") using index " +
                        "pctfree 10 initrans 2 maxtrans 255 storage ( initial 7M next 1M minextents 1 maxextents unlimited )";
                microTablemetaDao.executeBySql(strSql);
            }
            String index = "IND_" + tableName + "_" + ideCode; //INDEX->IND 避免索引名过长报错
            strSql = "create index " + index + " on " + tableName + " (" + ideCode + ")";
            System.out.println(strSql);
            try {
                microTablemetaDao.executeBySql(strSql);
            }catch (Exception e){

            }
        }
        return true;
    }

    private boolean addComments(String tableName, String cnName, List<TMicroIdenmeta> idenList) {

        String sql = "comment on table " + tableName + " is '" + cnName + "'";
        microTablemetaDao.executeBySql(sql);

        for (TMicroIdenmeta meta : idenList) {
            String ideCode = meta.getIdenCode();
            String ideName = meta.getIdenName();
            sql = "comment on column " + tableName + "." + ideCode + " is '" + ideName + "'";
            try {
                microTablemetaDao.executeBySql(sql);
            } catch (Exception e){

            }
        }
        return true;
    }

    ;

    private List<TMicroIdenmeta> getIdenCodeList(int mitmid) {
        List<TMicroIdenmeta> idensList = microIdenmetaDao.findObjectIdens(mitmid);
        return idensList;
    }

    /**
     * 导出调查对象导入模板
     * @param microid
     * @param type
     * @return
     */
    public HSSFWorkbook downloadTemplate(int microid,String type){
        HSSFWorkbook wk = new HSSFWorkbook();
        TMicroTablemeta t = microTablemetaDao.findOne(microid) ;
        if(null == t){
            return  wk ;
        }
        List<TMicroIdenmeta> indicators = microIdenmetaDao.findObjectIdens(microid);
        int size = indicators.size() ;
        if(type.equals("excel")){
            size +=2;
        }
        String[] head = new String[size] ;
        int index =0 ;
        if(size>indicators.size()){
            head[0] ="X坐标";
            head[1] ="Y坐标";
            index =2;
        }
        for(TMicroIdenmeta idenmeta:indicators){
            head[index++] = idenmeta.getIdenName();
        }
        wk = ExcelUtil.dataToWorkbook("基层数据导入模板",head,new String[0][]);
        return  wk ;
    }

    /**
     * 基层数据表当前最大ID
     *
     * @param tableName 基层数据表名
     * @return
     */
    public int getDataTableId(String tableName) {
        //最大ID
        int maxID = this.microTablemetaDao.findIntResult("select max(to_number(ID)) from " + tableName);
        return maxID;
    }
}
